package com.itmate.wei.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.itmate.wei.db.DBUtil;
import com.itmate.wei.entity.Family;
import com.itmate.wei.entity.Member;


public class MemberDao extends BaseDao{
	/**
	 * 添加数据
	 * @param m
	 * @return
	 */
	public int AddMember(Member m){
		int i=-1;
		try {
			connection=DBUtil.getConnection();
		preparedStatement=connection.prepareStatement("insert into home_member (name,relation,family) values ( ?,?,? )");
		preparedStatement.setString(1,m.getName());
		preparedStatement.setString(2, m.getRelation());
		preparedStatement.setString(3, m.getFamily());
		i=preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			closed();
		}
		return i;
	}
	/**
	 * 模糊查询
	 * @param text 模糊查询的关键字
	 * @return
	 */
	public List<Member> SelectLikeName(String text){
		List<Member> list=new ArrayList<Member>();
		try {
			connection=DBUtil.getConnection();
		preparedStatement=connection.prepareStatement(
				"select * from home_member where name like ?");
		preparedStatement.setString(1,"%"+text+"%");
		resultSet=preparedStatement.executeQuery();
		while(resultSet.next()){
			int id=resultSet.getInt("id");
			String name=resultSet.getString("name");
			String relation=resultSet.getString("relation");
			String family=resultSet.getString("family");
			Member m=new Member();
			m.setId(id);
			m.setName(name);
			m.setRelation(relation);
			m.setFamily(family);
			list.add(m);
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closed();
		}
		return list;
	}
	/**
	 * 修改数据的方法
	 * @param a
	 * @return
	 */
	public int UpdateMember(Member a){
		int i=-1;
		connection=DBUtil.getConnection();
		try {
			preparedStatement=connection.prepareStatement(
					"update home_member set name=?,relation=? ,family=? where id=?");
			preparedStatement.setString(1, a.getName());
			preparedStatement.setString(2, a.getRelation());
			preparedStatement.setString(3, a.getFamily());
			preparedStatement.setInt(4, a.getId());
			i=preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closed();
		}
		return i;
	}
	/**
	 * 删除数据的方法
	 * @param id
	 * @return
	 */
	public int DeleteByID(int id){
		int i=-1;
		try {
			connection=DBUtil.getConnection();
		preparedStatement=connection.prepareStatement("delete from home_member where id= ?");
		preparedStatement.setInt(1, id);
		i=preparedStatement.executeUpdate();	
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			closed();
		}
		return i;
	}
	/**
	 * 查询所有数据
	 * @return
	 */
	public List<Member> SelectAllMember(){
		List<Member> list =new ArrayList<Member>();
		try {
			connection=DBUtil.getConnection();
			preparedStatement=connection.prepareStatement("select * from home_member");
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()){
				int id=resultSet.getInt("id");
				String name=resultSet.getString("name");
				String relation=resultSet.getString("relation");
				String family=resultSet.getString("family");
				Member m=new Member();
				m.setId(id);
				m.setName(name);
				m.setRelation(relation);
				m.setFamily(family);
				list.add(m);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closed();
		}
		return list;
	}
	public List<Family> SelectAllFamily(){
		List<Family> familylist=new ArrayList<>();
		try {
			connection = DBUtil.getConnection();
			preparedStatement=connection.prepareStatement("select * from family");
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()){
				Family f = new Family();
				f.setId(resultSet.getInt("id"));
				f.setName(resultSet.getString("name"));
				familylist.add(f);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closed();
		}
		return familylist;
	}
}
